﻿Imports System.Data.OleDb
Public Class ShopDetails
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsPostBack = False Then
            If Request.Params("ID") = "" Then Response.Redirect("ShopList.aspx")
            Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringRestaurants").ConnectionString)
            'select all fields from Shop Comments table where fkID from Query string is correct shop
            Dim SqlString As String = "SELECT * FROM ShopCommentsView WHERE [Shop_fkID]=@f1"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd.CommandType = CommandType.Text
            'declare f1 variable to provide query string ID
            cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            'as data will be returned, use ExecuteReader not ExecuteNonQuery
            oleDbConn.Open()
            Dim dataReader = cmd.ExecuteReader()
            GridView2.DataSource = dataReader
            GridView2.DataBind()

            'log when a user views a shop
            Dim ShopsViewedSql As String = "INSERT INTO ShopsViewed(Viewer, Shop_fkID) VALUES (@f1,@f2)"
            Dim cmd2 As OleDbCommand = New OleDbCommand(ShopsViewedSql, oleDbConn)
            cmd2.CommandType = CommandType.Text
            cmd2.Parameters.AddWithValue("@f1", User.Identity.Name)
            cmd2.Parameters.AddWithValue("@f2", Request.Params("ID"))
            cmd2.ExecuteNonQuery()

        End If
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim myGUID As New System.Guid
        myGUID = Guid.NewGuid()
        Dim newPictureName = myGUID.ToString() + ".jpg"

        If f_Picture.HasFile Then
            f_Picture.SaveAs(Request.MapPath("~/Pictures") & "/" & newPictureName)
        End If


        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringRestaurants").ConnectionString)
        Dim SqlString As String = "Insert into ShopComments([UserName],[Comment_title],[Comment],[Glutenfree_rating],[Photo],[Price],[Availability],[Range],[Shop_fkID]) Values (@f1,@f2,@f3,@f4,@f5,@f6,@f7,@f8,@f9)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f2", tb_CommentTitle.Text)
        cmd.Parameters.AddWithValue("@f3", tb_Comment.Text)
        cmd.Parameters.AddWithValue("@f4", rbl_GFRating.SelectedValue)
        cmd.Parameters.AddWithValue("@f5", "~/Pictures/" & newPictureName)
        cmd.Parameters.AddWithValue("@f6", rbl_PriceBracket.SelectedValue)
        cmd.Parameters.AddWithValue("@f7", rbl_Availibility.SelectedValue)
        cmd.Parameters.AddWithValue("@f8", rbl_Range.SelectedValue)
        cmd.Parameters.AddWithValue("@f9", Request.Params("ID"))
        oleDbConn.Open()
        cmd.ExecuteNonQuery()

        'fill gridview2 if isPostBack is true i.e. if the comment has already been added.
        Dim fillComments As String = "SELECT * FROM ShopCommentsView WHERE Shop_fkID=@f1"
        Dim fillCommentsCmd As OleDbCommand = New OleDbCommand(fillComments, oleDbConn)
        fillCommentsCmd.CommandType = CommandType.Text
        fillCommentsCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        Dim dataReader = fillCommentsCmd.ExecuteReader()
        GridView2.DataSource = dataReader
        GridView2.DataBind()
        'this will show the last added comment in the list of comments.
        tb_Comment.Text = ""
        tb_CommentTitle.Text = ""
        rbl_GFRating.SelectedValue = ""
        rbl_PriceBracket.SelectedValue = ""
        rbl_Availibility.SelectedValue = ""
        rbl_Range.SelectedValue = ""
    End Sub

    Protected Sub btn_TagShop_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_TagShop.Click
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionStringRestaurants").ConnectionString)
        Dim SqlString As String = "INSERT INTO ShopTagJunction([Shop_fkID],[Tag_fkID]) VALUES (@f1,@f2) "
        oleDbConn.Open()
        'loop through to see which tag or tags are selected
        For Each cbl As ListItem In cbl_ShopTag.Items
            If cbl.Selected Then
                'add join to the tags and shop table
                Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
                cmd.Parameters.AddWithValue("@f2", cbl.Value)
                cmd.ExecuteNonQuery()
                'once value added to db, tick is removed from page.
                cbl.Selected = False
            End If
        Next
        'If list of new tags is empty then ignore the rest of the function.
        If tb_tags.Text = "" Then Return

        'split new tags textbox and break into an array
        Dim newTags = tb_tags.Text.Split(",")
        ' newTags is temporary array, newTag is temporary variable
        For Each newTag In newTags
            'add to the table of tags
            Dim newTagSql As String = "INSERT into Tags([Tag]) VALUES(@f1)"
            Dim cmd As OleDbCommand = New OleDbCommand(newTagSql, oleDbConn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", newTag)
            cmd.ExecuteNonQuery()

            'find the id of the new tag in the table
            'select the top result of the table when the ID is sorted descending (i.e. the highest number will be at the top)
            Dim getLastIDComd As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID from Tags ORDER BY ID DESC", oleDbConn)
            Dim dataReader2 = getLastIDComd.ExecuteReader()
            dataReader2.Read()
            Dim lastID = dataReader2.GetValue(0)

            'link the new tag ID with the restaurantID in the junction table
            Dim cmd1 As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd1.CommandType = CommandType.Text
            cmd1.Parameters.AddWithValue("@f1", Request.Params("ID"))
            cmd1.Parameters.AddWithValue("@f2", lastID)
            cmd1.ExecuteNonQuery()

            'clear the Textbox with new tags in it
            tb_tags.Text = ""
        Next

    End Sub


End Class